package com.epoint.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.util.StringUtils;
import com.epoint.entity.QuoteInfo;
import com.epoint.utils.JDBCUtils;

/**
 * @Description 
 * @author ylc
 */
public class QuoteInfoDao {

	public void setQuote(QuoteInfo quoteInfo, ResultSet rs) throws SQLException {
		quoteInfo.setSupplierId(rs.getString("supplierId"));
		quoteInfo.setSupplierName(rs.getString("supplierName"));
		quoteInfo.setProductId(rs.getString("productId"));
		quoteInfo.setPhone(rs.getString("phone"));
		quoteInfo.setEmail(rs.getString("email"));
		quoteInfo.setQuote(rs.getBigDecimal("quote"));
		quoteInfo.setCreateTime(rs.getTimestamp("createTime"));
	}
	/**
	 * 获取报价信息列表
	 * @param valueOf
	 * @param valueOf2pageIndex
	 * @param sortField
	 * @param sortOrder
	 * @return
	 */
	public List<QuoteInfo> getQuoteList(int pageIndex, int pageSize, String sortField, String sortOrder) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<QuoteInfo> quoteList = new ArrayList<>();
		List<Object> params = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("select a.*, b.productName from quoteinfo a inner join productinfo b on a.productid = b.productid where 1=1");

		// 排序条件
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			sb.append(" order by " + sortField + " " + sortOrder);
		}
		// 分页条件
		if (-1 != pageIndex && -1 != pageSize) {
			sb.append(" limit ?,?");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = sb.toString();
			pstmt = conn.prepareStatement(sql);

			// 给?赋值
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pstmt.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pstmt.setString(i + 1, params.get(i).toString());
				}
			}
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				QuoteInfo quoteInfo = new QuoteInfo();
				setQuote(quoteInfo,rs);
				quoteInfo.setProductName(rs.getString("productName"));
				quoteList.add(quoteInfo);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}

		return quoteList;
	}

	/**
	 * 获取报价列表总数
	 * @return
	 */
	public int getQuoteListCount() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select count(1) from quoteinfo where 1=1";
			pstmt = conn.prepareStatement(sql);
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}
	/**
	 * 根据supplierId删除报价信息,同时更新产品信息表中的信息
	 * @param supplierId
	 * @param productId
	 * @return
	 */
	public int deleteQuoteInfoById(String supplierId, String productId) {
		Connection conn = null;
		PreparedStatement pstmtProduct = null;
		PreparedStatement pstmtQuote = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			// 关闭自动提交，开启事务
			conn.setAutoCommit(false);
			
			// 删除报价信息
			String sql1 = "DELETE FROM quotationManage.quoteinfo WHERE supplierid=?";
			pstmtQuote = conn.prepareStatement(sql1);
			pstmtQuote.setString(1, supplierId);
			res = pstmtQuote.executeUpdate();
			
			// （1）更新“产品信息”表中的“总报价次”数-1 （2）更新“最新报价”为上一次的报价。
			String sql2 = "UPDATE quotationManage.productinfo\r\n" + 
					"SET count=count-1, quote=(\r\n" + 
					"	select quote \r\n" + 
					"	from quoteinfo\r\n" + 
					"	where productid = ?\r\n" + 
					"	order by createtime desc \r\n" + 
					"	limit 1\r\n" + 
					")\r\n" + 
					"WHERE productid=?";
			pstmtProduct = conn.prepareStatement(sql2);
			pstmtProduct.setString(1, productId);
			pstmtProduct.setString(2, productId);
			res = pstmtProduct.executeUpdate();
			
			// 提交
			conn.commit();
		} catch (SQLException e) {
			// 回滚
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmtQuote);
			JDBCUtils.close(pstmtProduct, conn);
		}
		return res;
	}
	/**
	 * 添加报价信息
	 * @param quoteInfo
	 * @return
	 */
	public int addQuoteInfo(QuoteInfo quoteInfo) {
		Connection conn = null;
		PreparedStatement pstmtUpdate = null;
		PreparedStatement pstmtInsesrt = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			conn.setAutoCommit(false);
			//需要更新“产品信息表”中对应产品的最新报价为当前报价，同时更新对应产品的报价次数+1。
			String sql1 = "UPDATE quotationManage.productinfo SET count=count+1, quote=? where productid = ?";
			pstmtUpdate = conn.prepareStatement(sql1);
			pstmtUpdate.setBigDecimal(1, quoteInfo.getQuote());
			pstmtUpdate.setString(2, quoteInfo.getProductId());
			res = pstmtUpdate.executeUpdate();
			
			String sql2 = "INSERT INTO quotationManage.quoteinfo (supplierid, suppliername, productid, phone, email, quote, createtime) VALUES(?, ?, ?, ?, ?, ?, ?)";
			pstmtInsesrt = conn.prepareStatement(sql2);
			pstmtInsesrt.setString(1, quoteInfo.getSupplierId());
			pstmtInsesrt.setString(2, quoteInfo.getSupplierName());
			pstmtInsesrt.setString(3, quoteInfo.getProductId());
			pstmtInsesrt.setString(4, quoteInfo.getPhone());
			pstmtInsesrt.setString(5, quoteInfo.getEmail());
			pstmtInsesrt.setBigDecimal(6, quoteInfo.getQuote());
			Timestamp date = new Timestamp(quoteInfo.getCreateTime().getTime());
			pstmtInsesrt.setTimestamp(7, date);
			
			// 执行查询，获取结果集
			res = pstmtInsesrt.executeUpdate();

			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmtUpdate);
			JDBCUtils.close(pstmtInsesrt, conn);
		}
		return res;
	}
	/**
	 * 获取当前供应商今日报价次数
	 * @return
	 */
	public int isQuoteCount(String supplierName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int res = 0;
		
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select count(1) from quoteinfo where date_format(createtime ,'%Y-%m-%d' ) = date_format(now() ,'%Y-%m-%d') and supplierName = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, supplierName);
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}

}
